<?php if (!defined('BASEPATH')) exit('No direct script access allowed');

class mdl_khs extends CI_Model {
    
	function __construct(){
        parent::__construct();
    }
	
	function getdata2($plimit=true){
		# get parameter from easy grid
		$page = isset($_POST['page']) ? intval($_POST['page']) : 1;  
		$limit = isset($_POST['rows']) ? intval($_POST['rows']) : 10;
		$sort = isset($_POST['sort']) ? strval($_POST['sort']) : 'a.nim';  
		$order = isset($_POST['order']) ? strval($_POST['order']) : 'asc';  
		$offset = ($page-1)*$limit;
		
		#get filter
		$kd_fakultas = isset($_POST['kd_fakultas']) ? strval($_POST['kd_fakultas']) : '';
		$kd_prodi = isset($_POST['kd_prodi']) ? strval($_POST['kd_prodi']) : '';
		$tahun = isset($_POST['tahun']) ? strval($_POST['tahun']) : '';
		
		
		# create query
		$this->db->flush_cache();
		$this->db->start_cache();
			$this->db->select('a.id_krs, a.nim, b.nama, c.nama_dosen, a.semester, sum(e.sks) as total_sks, count(e.kd_matakuliah) as total_mk,
				(SELECT format((sum(t.sks*u.bobot)/sum(t.sks)),2)
				FROM tbl_krs_detail s
				JOIN ref_matakuliah t ON t.kd_matakuliah = s.kd_matakuliah
				JOIN ref_bobot u ON u.kd_bobot = s.nilai_huruf
				JOIN tbl_krs v ON v.id_krs = s.id_krs
				WHERE s.id_krs = a.id_krs
				GROUP BY v.nim) as ips1',false);
			$this->db->from('tbl_krs a');
			$this->db->join('tbl_mahasiswa b', 'b.nim = a.nim');
			$this->db->join('ref_dosen c', 'c.kd_dosen = a.kd_dosen');
			$this->db->join('tbl_krs_detail d', 'd.id_krs = a.id_krs');
			$this->db->join('ref_matakuliah e', 'e.kd_matakuliah = d.kd_matakuliah');
			
			$this->db->where('a.kd_fakultas', $kd_fakultas);
			$this->db->where('a.kd_prodi', $kd_prodi);
			$this->db->where('a.id_tahunajaran', $tahun);
			
			$this->db->group_by('id_krs');
			
			$this->db->order_by($sort, $order);
		$this->db->stop_cache();
		
		# get count
		$tmp['row_count'] = $this->db->get()->num_rows();
		
		# get data
		if($plimit == true){
			$this->db->limit($limit, $offset);
		}
		$tmp['row_data'] = $this->db->get();
		
		return $tmp;
	}
	
	function getdata(){
		# get parameter from easy grid
		$page = isset($_POST['page']) ? intval($_POST['page']) : 1;  
		$limit = isset($_POST['rows']) ? intval($_POST['rows']) : 10;
		$sort = isset($_POST['sort']) ? strval($_POST['sort']) : 'nama_matakuliah';  
		$order = isset($_POST['order']) ? strval($_POST['order']) : 'asc';  
		$offset = ($page-1)*$limit;
		
		#get filter
		$kd_fakultas = isset($_POST['kd_fakultas']) ? strval($_POST['kd_fakultas']) : '';
		$kd_prodi = isset($_POST['kd_prodi']) ? strval($_POST['kd_prodi']) : '';
		$tahun = isset($_POST['tahun']) ? strval($_POST['tahun']) : '';
		$nim = isset($_POST['nim']) ? strval($_POST['nim']) : '';
		
		//$sort = isset($_POST['sort']) ? strval($_POST['sort']) : 'nama_matakuliah';  
		//$order = isset($_POST['order']) ? strval($_POST['order']) : 'asc';  
		
		# create query
		$this->db->flush_cache();
		$this->db->start_cache();
			$this->db->select('a.kd_matakuliah,a.nilai_huruf ,b.nama_matakuliah, b.sks, b.semester, c.nim, c.kd_fakultas, c.kd_prodi, c.id_tahunajaran,
			(SELECT format((sum(t.sks*u.bobot)/sum(t.sks)),2)
				FROM tbl_krs_detail s
				JOIN ref_matakuliah t ON t.kd_matakuliah = s.kd_matakuliah
				JOIN ref_bobot u ON u.kd_bobot = s.nilai_huruf
				JOIN tbl_krs v ON v.id_krs = s.id_krs
				WHERE s.id_krs = a.id_krs
				GROUP BY v.nim) as ips1',false);
			$this->db->from('tbl_krs_detail a');
			$this->db->join('ref_matakuliah b', 'b.kd_matakuliah = a.kd_matakuliah');
			$this->db->join('tbl_krs c', 'c.id_krs = a.id_krs');
			
			$this->db->where('c.kd_fakultas', $kd_fakultas);
			$this->db->where('c.kd_prodi', $kd_prodi);
			$this->db->where('c.id_tahunajaran', $tahun);
			$this->db->where('c.nim', $nim);
			$this->db->order_by($sort, $order);
		$this->db->stop_cache();
		
		# get count
		$tmp['row_count'] = $this->db->get()->num_rows();
		
		# get data
		$tmp['row_data'] = $this->db->get();
		
		return $tmp;
	}
	
	function getdata_detail($id){
		$sort = isset($_POST['sort']) ? strval($_POST['sort']) : 'nama_matakuliah';  
		$order = isset($_POST['order']) ? strval($_POST['order']) : 'asc';  
		
		# create query
		$this->db->flush_cache();
		$this->db->start_cache();
			$this->db->select('a.kd_matakuliah,a.nilai_huruf ,b.nama_matakuliah, b.sks, b.semester');
			$this->db->from('tbl_krs_detail a');
			$this->db->join('ref_matakuliah b', 'b.kd_matakuliah = a.kd_matakuliah');
			$this->db->where('a.id_krs', $id);
			$this->db->order_by($sort, $order);
		$this->db->stop_cache();
		
		# get count
		$tmp['row_count'] = $this->db->get()->num_rows();
		
		# get data
		$tmp['row_data'] = $this->db->get();
		
		return $tmp;
	}
	
	function togrid($data, $count){
		$ips = 0;
		$sks = 0;
		$response->total = $count;
		$response->rows = array();
		if($count>0){
			$i=0;
			foreach($data->result_array() as $row){
				foreach($row as $key => $value){
					$response->rows[$i][$key]=$value;
					if($key=="ips1"){$ips=$value;}
					elseif($key=="sks"){$sks+=$value;}
				}
				$i++;
			}
		}
		$response->footer[0]['nama_matakuliah']='Total SKS';
		$response->footer[0]['sks']=$sks;
		$response->footer[0]['semester']='IPS';
		$response->footer[0]['nilai_huruf']=$ips;
		//echo json_encode($response);
		return json_encode($response);
	}
	
	function getmatakuliah($kd_kurikulum, $kd_prodi){
		# create query
		$this->db->flush_cache();
		$this->db->start_cache();
			$this->db->select('kd_matakuliah, nama_matakuliah, sks, semester, kd_jenismk');
			$this->db->from('ref_matakuliah');
			$this->db->where('kd_kurikulum', $kd_kurikulum);
			$this->db->where('kd_prodi', $kd_prodi);
			$this->db->order_by('semester' , 'ASC');
			$this->db->order_by('kd_matakuliah' , 'ASC');
		$this->db->stop_cache();
		
		# get count
		$tmp['row_count'] = $this->db->get()->num_rows();
		
		# get data		
		$tmp['row_data'] = $this->db->get();
		
		return $tmp;
		
	}
	
	function getsemester_mhs($nim){
		$this->db->flush_cache();
		$this->db->select('semester_mhsw');
		$this->db->where('nim', $nim);
		$this->db->where('semester !=', 'SP');
		$this->db->from('tbl_krs');
		$this->db->order_by('semester_mhsw', 'DESC');
		
		$r = $this->db->get();
		
		if($r->num_rows() > 0){
			return $r->row()->semester_mhsw + 1;
		}else{
			return 1;
		}
	}
	
	function getnomor(){
		$this->db->flush_cache();
		$query1 = $this->db->query("SHOW TABLE STATUS LIKE 'tbl_krs'");
		return $query1->row()->Auto_increment;
	}
	
	function InsertOnDb($data){
		$this->db->trans_start();
		
		# insert to krs
		$this->db->flush_cache();
        //$this->db->set('kd_pt', $data['kd_pt']);
        $this->db->set('kd_fakultas', $data['kd_fakultas']);
        $this->db->set('kd_prodi', $data['kd_prodi']);
        $this->db->set('nim', $data['nim']);
        $this->db->set('id_tahunajaran', $data['id_tahunajaran']);
		
		if($data['semester'] == 'SP'){
			$this->db->set('semester', 'SP');
			$this->db->set('semester_mhsw', '0');
		}else{
			$this->db->set('semester', substr($data['id_tahunajaran'], 4, 1) == 1?'Ganjil':'Genap');
			$this->db->set('semester_mhsw', $data['semester']);
		}
        
		$this->db->set('kd_dosen', $data['kd_dosen']);
        //$this->db->set('jum_sksk', $data['jum_sksk']);
        $this->db->set('ipk', '0');
        $this->db->set('jum_skss', $data['sks']);
        //$this->db->set('ips', $data['ips']);
        $this->db->set('CUID', $this->session->userdata('user_id'));
        $this->db->set('CDATE', date('Y-m-d H:i:s'));
        $this->db->set('status', 'Approve');
		
		$this->db->insert('tbl_krs');
		
		foreach($data['kd_matakuliah'] as $row){
			$this->db->flush_cache();
			$this->db->set('id_krs', $data['nomor']);
			$this->db->set('kd_matakuliah', $row);
			$this->db->set('CUID', $this->session->userdata('user_id'));
			$this->db->set('CDATE', date('Y-m-d H:i:s'));
			
			$this->db->insert('tbl_krs_detail');
		}
		
		$this->db->trans_complete();
	    return $this->db->trans_status();
	}
	
	
	/* function get_pdf($fil,&$nama_dosen){
		
		# get data
		$this->db->flush_cache();
		$this->db->start_cache();
			$this->db->select('a.kd_matakuliah,a.nilai_huruf ,b.nama_matakuliah, b.sks, b.semester, c.nim, c.kd_fakultas, c.kd_prodi, c.id_tahunajaran, z.nama_dosen,
			(SELECT format((sum(t.sks*u.bobot)/sum(t.sks)),2)
				FROM tbl_krs_detail s
				JOIN ref_matakuliah t ON t.kd_matakuliah = s.kd_matakuliah
				JOIN ref_bobot u ON u.kd_bobot = s.nilai_huruf
				JOIN tbl_krs v ON v.id_krs = s.id_krs
				WHERE s.id_krs = a.id_krs
				GROUP BY v.nim) as ips1',false);
			$this->db->from('tbl_krs_detail a');
			$this->db->join('ref_matakuliah b', 'b.kd_matakuliah = a.kd_matakuliah');
			$this->db->join('tbl_krs c', 'c.id_krs = a.id_krs');
			$this->db->join('ref_dosen z', 'z.kd_dosen = c.kd_dosen');
			
			$this->db->where('c.kd_fakultas', $fil['kd_fakultas']);
			$this->db->where('c.kd_prodi', $fil['kd_prodi']);
			$this->db->where('c.id_tahunajaran', $fil['tahun']);
			$this->db->where('c.nim', $fil['nim']);

		// proses
		$result = $this->db->get();
		
		# init column
		$pdfdata['column'] = array();
		array_push($pdfdata['column'], array('caption'=>'No.', 'width'=>10, 'align'=>'L', 'val_align'=>'L'));
		array_push($pdfdata['column'], array('caption'=>'Kode MK', 'width'=>30, 'align'=>'L', 'val_align'=>'L'));
		array_push($pdfdata['column'], array('caption'=>'Makatkuliah', 'width'=>100, 'align'=>'L', 'val_align'=>'L'));
		array_push($pdfdata['column'], array('caption'=>'SKS', 'width'=>30, 'align'=>'L', 'val_align'=>'L'));
		array_push($pdfdata['column'], array('caption'=>'Semester', 'width'=>30, 'align'=>'L', 'val_align'=>'L'));
		array_push($pdfdata['column'], array('caption'=>'Nilai', 'width'=>30, 'align'=>'L', 'val_align'=>'L'));
		array_push($pdfdata['column'], array('caption'=>'IPS', 'width'=>30, 'align'=>'L', 'val_align'=>'L'));
		
		# init record
		$i=1;
		$pdfdata['row'] = array();
		foreach($result->result() as $row){
			array_push($pdfdata['row'], array(
				$i,
				$row->kd_matakuliah,
				$row->nama_matakuliah,
				$row->sks,
				$row->semester,
				$row->nilai_huruf,
				$row->ips1
			));
			$nama_dosen = $row->nama_dosen;
			$i++;
		}
		
		return $pdfdata;
		
	} */
	
	function getNameByNim($fil){
		$this->db->flush_cache();
		$this->db->from('tbl_mahasiswa');
		$this->db->order_by('nama');
		$this->db->where('nim', $fil);
		
		$res = $this->db->get()->row();
		
		return $res->nama;
	}
	
	function getFakultasByKode($fil){
		$this->db->flush_cache();
		$this->db->from('ref_fakultas');
		$this->db->order_by('nama_fakultas');
		$this->db->where('kd_fakultas', $fil);
		
		$res = $this->db->get()->row();
		
		return $res->nama_fakultas;
	}
	
	function getProdiByKode($fil){
		$this->db->flush_cache();
		$this->db->from('ref_prodi');
		$this->db->order_by('nama_prodi');
		$this->db->where('kd_prodi', $fil);
		
		$res = $this->db->get()->row();
		
		return $res->nama_prodi;
	}
	
	function get_pdf($fil,&$nama_dosen){
		
		# get data
		$this->db->flush_cache();
		$this->db->start_cache();
			$this->db->select('a.kd_matakuliah,a.nilai_huruf ,b.nama_matakuliah, b.sks, b.semester, c.nim, c.kd_fakultas, c.kd_prodi, c.id_tahunajaran, f.nama as nama_tahunajaran,
			d.nama_fakultas, e.nama_prodi, g.nama, i.nama_dosen, DATE_FORMAT(f.TglNilai, \'%d-%m-%Y\') as tgl_nilai, 
			(SELECT format((sum(t.sks*u.bobot)/sum(t.sks)),2)
				FROM tbl_krs_detail s
				JOIN ref_matakuliah t ON t.kd_matakuliah = s.kd_matakuliah
				JOIN ref_bobot u ON u.kd_bobot = s.nilai_huruf
				JOIN tbl_krs v ON v.id_krs = s.id_krs
				WHERE s.id_krs = a.id_krs
				GROUP BY v.nim) as ips1',false);
			$this->db->from('tbl_krs_detail a');
			$this->db->join('ref_matakuliah b', 'b.kd_matakuliah = a.kd_matakuliah');
			$this->db->join('tbl_krs c', 'c.id_krs = a.id_krs');
			$this->db->join('ref_fakultas d', 'd.kd_fakultas = c.kd_fakultas');
			$this->db->join('ref_prodi e', 'e.kd_prodi = c.kd_prodi');
			$this->db->join('tbl_mahasiswa g', 'g.nim = c.nim');
			$this->db->join('tbl_jadwal h','h.id_jadwal = a.id_jadwal');
			$this->db->join('ref_dosen i', 'i.kd_dosen = c.kd_dosen');
			$this->db->join('tbl_tahunajaran f', 'f.id_tahunajaran = c.id_tahunajaran and f.kd_prodi = c.kd_prodi');
			/* $this->db->select('a.kd_matakuliah,a.nilai_huruf ,b.nama_matakuliah, b.sks, b.semester, c.nim, c.kd_fakultas, c.kd_prodi, c.id_tahunajaran, z.nama_dosen, 
			f.TglNilai, sum(b.sks) as totsks, x.nama_prodi, y.nama_fakultas, f.nama,
			(SELECT format((sum(t.sks*u.bobot)/sum(t.sks)),2)
				FROM tbl_krs_detail s
				JOIN ref_matakuliah t ON t.kd_matakuliah = s.kd_matakuliah
				JOIN ref_bobot u ON u.kd_bobot = s.nilai_huruf
				JOIN tbl_krs v ON v.id_krs = s.id_krs
				WHERE s.id_krs = a.id_krs
				GROUP BY v.nim) as ips1',false);
			$this->db->from('tbl_krs_detail a');
			$this->db->join('ref_matakuliah b', 'b.kd_matakuliah = a.kd_matakuliah');
			$this->db->join('tbl_krs c', 'c.id_krs = a.id_krs');
			$this->db->join('ref_dosen z', 'z.kd_dosen = c.kd_dosen');
			$this->db->join('ref_prodi x', 'x.kd_prodi = c.kd_prodi');
			$this->db->join('ref_fakultas y', 'y.kd_fakultas = c.kd_fakultas');
			$this->db->join('tbl_tahunajaran f', 'f.id_tahunajaran = c.id_tahunajaran and f.kd_prodi = c.kd_prodi'); */
			//$this->db->join('ref_bobot u', 'u.kd_bobot = a.nilai_huruf');
			
			$this->db->where('c.kd_fakultas', $fil['kd_fakultas']);
			$this->db->where('c.kd_prodi', $fil['kd_prodi']);
			$this->db->where('c.id_tahunajaran', $fil['tahun']);
			$this->db->where('c.nim', $fil['nim']);

		// proses
			$result = $this->db->get();
		
		 if ($result->num_rows() > 0) {
            foreach ($result->result() as $data) {
                $data_pdf[] = $data;
            }
            return $data_pdf;           
        }
		
	}
	
}

?>